﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Text;
using KPIS.GERP.SERVICE.DAL;
using KPIS.GERP.SERVICE.Model;
using KPIS.DBM;

namespace KPIS.GERP.SERVICE.DAL
{
    public class NonWorkingDays
    {
        Common common = new Common();
        DBManager dbManager = new DBManager(DataProvider.MySQL, ConfigurationSettings.AppSettings["ConnectionString"]);

        public List<hrm_ps_non_working_days> GetNonWorkingDays(string start_period, string end_period, string bg_year)
        {
            List<hrm_ps_non_working_days> result = new List<hrm_ps_non_working_days>();

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);
                dbManager.AddParameters(0, "@delete_status", Common.DeletedStatus);
                dbManager.AddParameters(1, "@start_period", start_period);
                dbManager.AddParameters(2, "@end_period", end_period);
                dbManager.AddParameters(3, "@bg_year", bg_year);
                //SELECT * FROM hrm_ps_non_working_days WHERE record_status <> 'D' AND bg_year = 2556 AND (non_working_date >= '2013-5-1' AND non_working_date <= '2013-5-31')
                const string strSQL = "select * from hrm_ps_non_working_days where record_status <> @delete_status and bg_year = @bg_year and (non_working_date >= @start_period and non_working_date <= @end_period)";
                                   // + (!string.IsNullOrWhiteSpace(start_period) ? " and non_working_date = @start_period" : "");
               
                DataTable dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];

                foreach (DataRow dr in dtb.Rows)
                {
                    result.Add(new hrm_ps_non_working_days()
                    {
                        non_working_seq = Convert.ToInt32(dr["non_working_seq"].ToString()),
                        bg_year = common.ConvertToNullableInt(dr["bg_year"].ToString()),
                        non_working_date = Convert.ToDateTime(dr["non_working_date"].ToString()),
                        non_working_name = dr["non_working_name"].ToString(),
                        remarks = dr["remarks"].ToString(),
                        record_status = dr["record_status"].ToString(),
                        created_by = common.ConvertToNullableInt(dr["created_by"].ToString()),
                        created_when = common.ConvertToNullableDateTime(dr["created_when"].ToString()),
                        updated_by = common.ConvertToNullableInt(dr["updated_by"].ToString()),
                        updated_when = common.ConvertToNullableDateTime(dr["updated_when"].ToString())
                    });
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (dbManager.Connection != null) { dbManager.Connection.Close(); dbManager.Dispose(); }
            }

            return result;
        }

        public List<hrm_ps_non_working_days> GetAllNonWorkingDays(string bg_year)
        {
            List<hrm_ps_non_working_days> result = new List<hrm_ps_non_working_days>();

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);
                dbManager.AddParameters(0, "@delete_status", Common.DeletedStatus);
                dbManager.AddParameters(1, "@bg_year", bg_year);
                //SELECT * FROM hrm_ps_non_working_days WHERE record_status <> 'D' AND bg_year = 2556 AND (non_working_date >= '2013-5-1' AND non_working_date <= '2013-5-31')
                const string strSQL = "select * from hrm_ps_non_working_days where record_status <> @delete_status and bg_year >= @bg_year";
                // + (!string.IsNullOrWhiteSpace(start_period) ? " and non_working_date = @start_period" : "");

                DataTable dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];

                foreach (DataRow dr in dtb.Rows)
                {
                    result.Add(new hrm_ps_non_working_days()
                    {
                        non_working_seq = Convert.ToInt32(dr["non_working_seq"].ToString()),
                        bg_year = common.ConvertToNullableInt(dr["bg_year"].ToString()),
                        non_working_date = Convert.ToDateTime(dr["non_working_date"].ToString()),
                        non_working_name = dr["non_working_name"].ToString(),
                        remarks = dr["remarks"].ToString(),
                        record_status = dr["record_status"].ToString(),
                        created_by = common.ConvertToNullableInt(dr["created_by"].ToString()),
                        created_when = common.ConvertToNullableDateTime(dr["created_when"].ToString()),
                        updated_by = common.ConvertToNullableInt(dr["updated_by"].ToString()),
                        updated_when = common.ConvertToNullableDateTime(dr["updated_when"].ToString())
                    });
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (dbManager.Connection != null) { dbManager.Connection.Close(); dbManager.Dispose(); }
            }

            return result;
        }

    }
}
